import json
import pandas as pd
import numpy as np

df = pd.read_excel("data2.xlsx")

result = []
for i, row in df.iterrows():
    colums = row.index.values
    d = {}
    for colum in colums:
        try:
            json_o = json.loads(row[colum])
            d['data'] = json_o['data']
            d['info'] = json_o['info']
            colum_dest = None
        except:
            d[colum] = row[colum]
    result.append(d)
df = pd.DataFrame(result)
df.dropna(axis=1, how='all', inplace=True)

df = df.explode('data')
df = df.explode('info')
df.reset_index(drop=True, inplace=True)


def formatrow(row, s_column, r_columns):
    s = row[s_column]
    if isinstance(s, dict):
        return [s.get(r_column) for r_column in r_columns]
    else:
        return [None] * len(r_columns)


df[['shapeType', 'points', 'info_tmp']] = df.apply(formatrow,
                            axis=1,
                            result_type="expand",
                            args=('data', ['shapeType', 'points', 'info']))
df = df.explode('info_tmp')
df['info_tmp'] = df['info_tmp'].apply(lambda s: s.get('val')
                                      if isinstance(s, dict) else np.nan)
df = df.explode('info_tmp')
df['realLabelValue'] = df['info_tmp'].apply(lambda s: s.get('realLabelValue')
                                            if isinstance(s, dict) else None)
df.drop(columns=['data', 'info_tmp'], inplace=True)

df.to_csv("result.csv",index=False)
